const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const cors = require('cors');

const app = express();
const port = 3000;
app.options('*', cors());  // 处理所有 OPTIONS 请求
// 中间件
app.use(cors());
//app.use(bodyParser.json());


// 增加请求体大小限制
app.use(bodyParser.json({ limit: '30mb' })); // 设置为 30MB
app.use(bodyParser.urlencoded({ limit: '30mb', extended: true })); // 使用 URL 编码的表单数据限制

// 创建数据库连接
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root', // MySQL 用户名
    password: 'root', // MySQL 密码
    database: 'ticketingsystem' // 数据库名
});

// 连接到数据库
db.connect((err) => {
    if (err) {
        console.error('数据库连接失败:', err);
        return;
    }
    console.log('成功连接到数据库');
});

// 注册用户
app.post('/register/user', (req, res) => {
    const { username, password } = req.body; // 只获取用户名和密码

    // 检查用户名是否已存在
    const checkQuery = 'SELECT * FROM users WHERE username = ?';
    db.query(checkQuery, [username], (err, results) => {
        if (err) {
            console.error('查询用户名失败:', err.message);
            return res.status(500).json({ message: '查询用户名失败', error: err.message });
        }

        if (results.length > 0) {
            // 用户名已存在
            return res.status(400).json({ message: '用户名已存在，请选择其他用户名' });
        }

        // 用户名不存在，继续注册
        const query = 'INSERT INTO users (username, password, role) VALUES (?, ?, ?)';
        db.query(query, [username, password, 'user'], (err, results) => {
            if (err) {
                console.error('用户注册失败:', err.message);
                return res.status(500).json({ message: '用户注册失败', error: err.message });
            }

            const userId = results.insertId; // 获取新插入用户的 ID
            console.log('新用户 ID:', userId); // 调试输出

            // 在 user_info 表中插入新用户信息
            const userInfoQuery = 'INSERT INTO user_info (user_id, nickname) VALUES (?, ?)';
            const nickname = username; // 使用用户名作为昵称
            db.query(userInfoQuery, [userId, nickname], (err) => {
                if (err) {
                    console.error('用户信息插入失败:', err.message); // 输出错误信息
                    return res.status(500).json({ message: '用户信息插入失败', error: err.message });
                }
                res.status(201).json({ message: '用户注册成功', userId });
            });
        });
    });
});

// 注册管理员
app.post('/register/admin', (req, res) => {
    const { username, password } = req.body; // 只获取用户名和密码

    // 插入管理员到 admins 表
    const query = 'INSERT INTO admins (username, password, role) VALUES (?, ?, ?)';
    db.query(query, [username, password, 'admin'], (err, results) => {
        if (err) {
            console.error('管理员注册失败:', err.message);
            return res.status(500).json({ message: '管理员注册失败', error: err.message });
        }

        const adminId = results.insertId; // 获取新插入管理员的 ID
        console.log('新管理员 ID:', adminId); // 调试输出

        // 在 admin_info 表中插入新管理员信息
        const adminInfoQuery = 'INSERT INTO admin_info (admin_id, nickname) VALUES (?, ?)'; // 使用 adminId 作为 user_id
        const nickname = username; // 使用用户名作为昵称
        db.query(adminInfoQuery, [adminId, nickname], (err) => {
            if (err) {
                console.error('管理员信息插入失败:', err.message); // 输出错误信息
                return res.status(500).json({ message: '管理员信息插入失败', error: err.message });
            }
            res.status(201).json({ message: '管理员注册成功', adminId });
        });
    });
});

// 登录 API
app.post('/login', (req, res) => {
    const { username, password, role } = req.body;

    // 根据角色选择查询的表
    const table = role === 'admin' ? 'admins' : 'users';
    const query = `SELECT * FROM ${table} WHERE username = ?`;

    db.query(query, [username], (err, results) => {
        if (err) {
            console.error('查询失败:', err);
            return res.status(500).json({ message: '登录失败' });
        }

        if (results.length > 0) {
            const user = results[0];
            // 直接比较明文密码
            if (password === user.password) {
                // 登录成功
                res.status(200).json({ 
                    message: '登录成功', 
                    role: user.role, // 返回角色
                    username: user.username // 返回用户名
                });
            } else {
                // 密码错误
                res.status(401).json({ message: '用户名或密码错误' });
            }
        } else {
            // 用户不存在
            res.status(404).json({ message: '用户不存在' });
        }
    });
});

// 获取用户列表（支持搜索）
app.get('/api/users', (req, res) => {
    const { page = 1, limit = 10, search } = req.query; // 搜索参数
    const offset = (page - 1) * limit;

    let query = 'SELECT * FROM users';
    let countQuery = 'SELECT COUNT(*) as total FROM users';
    const params = [];

    if (search) {
        query += ' WHERE username LIKE ? OR role LIKE ?';
        countQuery += ' WHERE username LIKE ? OR role LIKE ?';
        params.push(`%${search}%`, `%${search}%`);
    }

    query += ' LIMIT ? OFFSET ?';
    params.push(parseInt(limit), parseInt(offset));

    // 获取分页数据
    db.query(query, params, (err, results) => {
        if (err) {
            console.error('获取用户列表失败:', err);
            return res.status(500).json({ message: '获取用户列表失败', error: err.message });
        }

        // 获取总数
        db.query(countQuery, params.slice(0, -2), (err, countResults) => {
            if (err) {
                console.error('获取用户总数失败:', err);
                return res.status(500).json({ message: '获取用户总数失败', error: err.message });
            }

            res.json({
                data: results,
                total: countResults[0].total,
                page: parseInt(page),
                limit: parseInt(limit),
            });
        });
    });
});

// 获取管理员列表（支持搜索）
app.get('/api/admins', (req, res) => {
    const { page = 1, limit = 10, search } = req.query; // 搜索参数
    const offset = (page - 1) * limit;

    let query = 'SELECT * FROM admins';
    let countQuery = 'SELECT COUNT(*) as total FROM admins';
    const params = [];

    if (search) {
        query += ' WHERE username LIKE ? OR role LIKE ?';
        countQuery += ' WHERE username LIKE ? OR role LIKE ?';
        params.push(`%${search}%`, `%${search}%`);
    }

    query += ' LIMIT ? OFFSET ?';
    params.push(parseInt(limit), parseInt(offset));

    // 获取分页数据
    db.query(query, params, (err, results) => {
        if (err) {
            console.error('获取管理员列表失败:', err);
            return res.status(500).json({ message: '获取管理员列表失败', error: err.message });
        }

        // 获取总数
        db.query(countQuery, params.slice(0, -2), (err, countResults) => {
            if (err) {
                console.error('获取管理员总数失败:', err);
                return res.status(500).json({ message: '获取管理员总数失败', error: err.message });
            }

            res.json({
                data: results,
                total: countResults[0].total,
                page: parseInt(page),
                limit: parseInt(limit),
            });
        });
    });
});

//删除用户
app.delete('/api/users', (req, res) => {
    const userIds = req.body.ids; // 接收用户 ID 数组
    if (!userIds || !Array.isArray(userIds) || userIds.length === 0) {
        return res.status(400).json({ message: '参数无效' });
    }

    // 先删除 user_info 中的相关记录
    const deleteUserInfoQuery = 'DELETE FROM user_info WHERE user_id IN (?)';
    db.query(deleteUserInfoQuery, [userIds], (err) => {
        if (err) {
            console.error('删除用户信息失败:', err);
            return res.status(500).json({ message: '删除用户信息失败', error: err.message });
        }
        console.log(`用户信息 ${userIds.join(', ')} 删除成功`); // 调试输出

        // 然后删除 users 表中的用户
        const query = 'DELETE FROM users WHERE user_id IN (?)';
        db.query(query, [userIds], (err, results) => {
            if (err) {
                console.error('删除用户失败:', err);
                return res.status(500).json({ message: '删除用户失败', error: err.message });
            }
            if (results.affectedRows === 0) {
                console.log(`用户 ${userIds.join(', ')} 不存在，删除失败`); // 调试输出
                return res.status(404).json({ message: '用户不存在' });
            }
            console.log(`用户 ${userIds.join(', ')} 删除成功`); // 调试输出
            res.json({ message: '用户批量删除成功' });
        });
    });
});

//删除管理员
app.delete('/api/admins', (req, res) => {
    const adminIds = req.body.ids; // 接收管理员 ID 数组
    if (!adminIds || !Array.isArray(adminIds) || adminIds.length === 0) {
        return res.status(400).json({ message: '参数无效' });
    }

    // 先删除 admin_info 中的相关记录
    const deleteAdminInfoQuery = 'DELETE FROM admin_info WHERE admin_id IN (?)';
    db.query(deleteAdminInfoQuery, [adminIds], (err) => {
        if (err) {
            console.error('删除管理员信息失败:', err);
            return res.status(500).json({ message: '删除管理员信息失败', error: err.message });
        }
        console.log(`管理员信息 ${adminIds.join(', ')} 删除成功`); // 调试输出

        // 然后删除 admins 表中的管理员
        const query = 'DELETE FROM admins WHERE admin_id IN (?)';
        db.query(query, [adminIds], (err, results) => {
            if (err) {
                console.error('删除管理员失败:', err);
                return res.status(500).json({ message: '删除管理员失败', error: err.message });
            }
            if (results.affectedRows === 0) {
                console.log(`管理员 ${adminIds.join(', ')} 不存在，删除失败`); // 调试输出
                return res.status(404).json({ message: '管理员不存在' });
            }
            console.log(`管理员 ${adminIds.join(', ')} 删除成功`); // 调试输出
            res.json({ message: '管理员批量删除成功' });
        });
    });
});

// 获取用户信息
app.get('/api/user_info', (req, res) => {
    const { id, nickname, company, email, phone } = req.query; // 从查询参数中获取搜索条件
    let query = 'SELECT * FROM user_info WHERE 1=1'; // 基础查询

    const queryParams = []; // 用于存储查询参数

    // 根据提供的搜索条件构建查询
    if (id) {
        query += ' AND (info_id = ? OR user_id = ?)';
        queryParams.push(id, id); // info_id 和 user_id 搜索字段
    }
    if (nickname) {
        query += ' AND nickname LIKE ?';
        queryParams.push(`%${nickname}%`); // 使用 LIKE 进行模糊搜索
    }
    if (company) {
        query += ' AND company LIKE ?';
        queryParams.push(`%${company}%`);
    }
    if (email) {
        query += ' AND email LIKE ?';
        queryParams.push(`%${email}%`);
    }
    if (phone) {
        query += ' AND phone LIKE ?';
        queryParams.push(`%${phone}%`);
    }

    db.query(query, queryParams, (err, results) => {
        if (err) {
            console.error('获取用户信息失败:', err);
            return res.status(500).json({ message: '获取用户信息失败' });
        }
        res.json(results);
    });
});

// 获取管理员信息
app.get('/api/admin_info', (req, res) => {
    const { id, nickname, company, email, phone } = req.query; // 从查询参数中获取搜索条件
    let query = 'SELECT * FROM admin_info WHERE 1=1'; // 基础查询

    const queryParams = []; // 用于存储查询参数

    // 根据提供的搜索条件构建查询
    if (id) {
        query += ' AND admin_id = ?';
        queryParams.push(id);
    }
    if (nickname) {
        query += ' AND nickname LIKE ?';
        queryParams.push(`%${nickname}%`);
    }
    if (company) {
        query += ' AND company LIKE ?';
        queryParams.push(`%${company}%`);
    }
    if (email) {
        query += ' AND email LIKE ?';
        queryParams.push(`%${email}%`);
    }
    if (phone) {
        query += ' AND phone LIKE ?';
        queryParams.push(`%${phone}%`);
    }

    db.query(query, queryParams, (err, results) => {
        if (err) {
            console.error('获取管理员信息失败:', err);
            return res.status(500).json({ message: '获取管理员信息失败' });
        }
        res.json(results);
    });
});

//User_Info删除
// 删除用户
app.delete('/api/users_info', (req, res) => {
    const userIds = req.body.ids; // 从请求体中获取 ID 数组

    if (!Array.isArray(userIds) || userIds.length === 0) {
        return res.status(400).json({ message: '无效的用户 ID 数组' });
    }

    // 先删除 user_info 中的相关记录
    const deleteUserInfoQuery = 'DELETE FROM user_info WHERE user_id IN (?)';
    db.query(deleteUserInfoQuery, [userIds], (err) => {
        if (err) {
            console.error('删除用户信息失败:', err);
            return res.status(500).json({ message: '删除用户信息失败', error: err.message });
        }
        console.log(`用户信息 ${userIds.join(', ')} 删除成功`); // 调试输出

        // 然后删除 users 表中的用户
        const query = 'DELETE FROM users WHERE user_id IN (?)';
        db.query(query, [userIds], (err, results) => {
            if (err) {
                console.error('删除用户失败:', err);
                return res.status(500).json({ message: '删除用户失败', error: err.message });
            }
            console.log(`用户 ${userIds.join(', ')} 删除成功`); // 调试输出
            res.json({ message: '用户删除成功' });
        });
    });
});

// 删除管理员
// 批量删除管理员
app.delete('/api/admins_info', (req, res) => {
    const adminIds = req.body.ids; // 从请求体中获取 ID 数组

    if (!Array.isArray(adminIds) || adminIds.length === 0) {
        return res.status(400).json({ message: '无效的管理员 ID 数组' });
    }

    // 先删除 admin_info 中的相关记录
    const deleteAdminInfoQuery = 'DELETE FROM admin_info WHERE admin_id IN (?)';
    db.query(deleteAdminInfoQuery, [adminIds], (err) => {
        if (err) {
            console.error('删除管理员信息失败:', err);
            return res.status(500).json({ message: '删除管理员信息失败', error: err.message });
        }
        console.log(`管理员信息 ${adminIds.join(', ')} 删除成功`); // 调试输出

        // 然后删除 admins 表中的管理员
        const query = 'DELETE FROM admins WHERE admin_id IN (?)';
        db.query(query, [adminIds], (err, results) => {
            if (err) {
                console.error('删除管理员失败:', err);
                return res.status(500).json({ message: '删除管理员失败', error: err.message });
            }
            console.log(`管理员 ${adminIds.join(', ')} 删除成功`); // 调试输出
            res.json({ message: '管理员删除成功' });
        });
    });
});

// 更新用户信息
app.put('/api/old/user_info/:id', (req, res) => {
    const userId = req.params.id; // 获取用户 ID
    const updates = req.body; // 获取请求体中的更新数据
    const query = 'UPDATE user_info SET ? WHERE info_id = ?'; // 用户信息表的主键
    db.query(query, [updates, userId], (err, results) => {
        if (err) {
            console.error('更新用户信息失败:', err);
            return res.status(500).json({ message: '更新用户信息失败' });
        }
        res.json({ message: '用户信息更新成功' });
    });
});

// 更新管理员信息
app.put('/api/old/admin_info/:id', (req, res) => {
    const adminId = req.params.id; // 获取管理员 ID
    const updates = req.body; // 获取请求体中的更新数据
    const query = 'UPDATE admin_info SET ? WHERE info_id = ?'; // 管理员信息表的主键
    db.query(query, [updates, adminId], (err, results) => {
        if (err) {
            console.error('更新管理员信息失败:', err);
            return res.status(500).json({ message: '更新管理员信息失败' });
        }
        res.json({ message: '管理员信息更新成功' });
    });
});

// 启动服务器
app.listen(port, () => {
    console.log(`服务器正在运行在 http://localhost:${port}`);
});

//

// 获取用户信息通过用户名
app.get('/api/user_info/selfuserget', (req, res) => {
    const username = req.headers['username']; // 从请求头中获取用户名

    if (!username) {
        return res.status(400).json({ message: '缺少用户名' }); // 如果没有提供用户名，返回错误
    }

    // 查询用户 ID
    const userQuery = 'SELECT user_id FROM users WHERE username = ?';
    db.query(userQuery, [username], (err, userResults) => {
        if (err) {
            console.error('获取用户 ID 失败:', err);
            return res.status(500).json({ message: '获取用户 ID 失败' });
        }

        if (userResults.length === 0) {
            return res.status(404).json({ message: '用户未找到' });
        }

        const userId = userResults[0].user_id; // 获取用户 ID

        // 查询用户信息
        const infoQuery = 'SELECT * FROM user_info WHERE user_id = ?';
        db.query(infoQuery, [userId], (err, infoResults) => {
            if (err) {
                console.error('获取用户信息失败:', err);
                return res.status(500).json({ message: '获取用户信息失败' });
            }

            // 确保返回的数据是一个对象而不是数组
            res.json(infoResults.length > 0 ? infoResults[0] : {}); // 返回用户信息
        });
    });
});

// 获取管理员信息通过用户名
app.get('/api/admin_info/selfadminget', (req, res) => {
    const username = req.headers['username']; // 从请求头中获取用户名

    if (!username) {
        return res.status(400).json({ message: '缺少用户名' }); // 如果没有提供用户名，返回错误
    }

    // 查询管理员 ID
    const adminQuery = 'SELECT admin_id FROM admins WHERE username = ?';
    db.query(adminQuery, [username], (err, adminResults) => {
        if (err) {
            console.error('获取管理员 ID 失败:', err);
            return res.status(500).json({ message: '获取管理员 ID 失败' });
        }

        if (adminResults.length === 0) {
            return res.status(404).json({ message: '管理员未找到' });
        }

        const adminId = adminResults[0].admin_id; // 获取管理员 ID

        // 查询管理员信息
        const infoQuery = 'SELECT * FROM admin_info WHERE admin_id = ?';
        db.query(infoQuery, [adminId], (err, infoResults) => {
            if (err) {
                console.error('获取管理员信息失败:', err);
                return res.status(500).json({ message: '获取管理员信息失败' });
            }

            // 确保返回的数据是一个对象而不是数组
            res.json(infoResults.length > 0 ? infoResults[0] : {}); // 返回管理员信息
        });
    });
});

// 更新用户信息
app.put('/api/user_info/selfuserupdate', (req, res) => {
    const username = req.headers['username']; // 从请求头中获取用户名
    const updates = req.body; // 获取请求体中的更新数据

    if (!username) {
        return res.status(400).json({ message: '缺少用户名' }); // 如果没有提供用户名，返回错误
    }

    // 查询用户 ID
    const userQuery = 'SELECT user_id FROM users WHERE username = ?';
    db.query(userQuery, [username], (err, userResults) => {
        if (err) {
            console.error('获取用户 ID 失败:', err);
            return res.status(500).json({ message: '获取用户 ID 失败' });
        }

        if (userResults.length === 0) {
            return res.status(404).json({ message: '用户未找到' });
        }

        const userId = userResults[0].user_id; // 获取用户 ID

        // 更新用户信息
        const updateQuery = 'UPDATE user_info SET ? WHERE user_id = ?';
        db.query(updateQuery, [updates, userId], (err, results) => {
            if (err) {
                console.error('更新用户信息失败:', err);
                return res.status(500).json({ message: '更新用户信息失败' });
            }
            res.json({ message: '用户信息更新成功' });
        });
    });
});

// 更新管理员信息
app.put('/api/admin_info/selfadminupdate', (req, res) => {
    const username = req.headers['username']; // 从请求头中获取用户名
    const updates = req.body; // 获取请求体中的更新数据

    if (!username) {
        return res.status(400).json({ message: '缺少用户名' }); // 如果没有提供用户名，返回错误
    }

    // 查询管理员 ID
    const adminQuery = 'SELECT admin_id FROM admins WHERE username = ?';
    db.query(adminQuery, [username], (err, adminResults) => {
        if (err) {
            console.error('获取管理员 ID 失败:', err);
            return res.status(500).json({ message: '获取管理员 ID 失败' });
        }

        if (adminResults.length === 0) {
            return res.status(404).json({ message: '管理员未找到' });
        }

        const adminId = adminResults[0].admin_id; // 获取管理员 ID

        // 更新管理员信息
        const updateQuery = 'UPDATE admin_info SET ? WHERE admin_id = ?';
        db.query(updateQuery, [updates, adminId], (err, results) => {
            if (err) {
                console.error('更新管理员信息失败:', err);
                return res.status(500).json({ message: '更新管理员信息失败' });
            }
            res.json({ message: '管理员信息更新成功' });
        });
    });
});



// 修改用户密码
app.post('/api/users/change-password', (req, res) => {
    const { username, oldPassword, newPassword } = req.body;

    // 检查用户是否存在并验证原密码
    const query = 'SELECT * FROM users WHERE username = ?';
    db.query(query, [username], (err, results) => {
        if (err) {
            console.error('查询用户失败:', err);
            return res.status(500).json({ message: '查询用户失败' });
        }

        if (results.length === 0) {
            return res.status(404).json({ message: '用户不存在' });
        }

        const user = results[0];
        // 验证原密码
        if (oldPassword !== user.password) {
            return res.status(401).json({ message: '原密码错误' });
        }

        // 更新密码
        const updateQuery = 'UPDATE users SET password = ? WHERE username = ?';
        db.query(updateQuery, [newPassword, username], (err) => {
            if (err) {
                console.error('更新密码失败:', err);
                return res.status(500).json({ message: '更新密码失败' });
            }
            res.json({ message: '密码修改成功' });
        });
    });
});

// 修改管理员密码
app.post('/api/admins/change-password', (req, res) => {
    const { username, oldPassword, newPassword } = req.body;

    // 检查管理员是否存在并验证原密码
    const query = 'SELECT * FROM admins WHERE username = ?';
    db.query(query, [username], (err, results) => {
        if (err) {
            console.error('查询管理员失败:', err);
            return res.status(500).json({ message: '查询管理员失败' });
        }

        if (results.length === 0) {
            return res.status(404).json({ message: '管理员不存在' });
        }

        const admin = results[0];
        // 验证原密码
        if (oldPassword !== admin.password) {
            return res.status(401).json({ message: '原密码错误' });
        }

        // 更新密码
        const updateQuery = 'UPDATE admins SET password = ? WHERE username = ?';
        db.query(updateQuery, [newPassword, username], (err) => {
            if (err) {
                console.error('更新密码失败:', err);
                return res.status(500).json({ message: '更新密码失败' });
            }
            res.json({ message: '密码修改成功' });
        });
    });
});


// 处理上传的 incidents 数据
app.post('/api/incidents', (req, res) => {
    const incidents = Array.isArray(req.body) ? req.body : [req.body]; // 确保是数组

    // 检查请求数据格式
    if (incidents.length === 0) {
        return res.status(400).json({ message: '请求数据格式不正确，应该是一个数组或单个对象' });
    }

    // 查询当前最大 id
    db.query('SELECT MAX(id) AS maxId FROM incidents', (err, results) => {
        if (err) {
            return res.status(500).json({ error: '数据库查询失败' });
        }

        const maxId = results[0].maxId || 9999999; // 如果没有记录，设置为 9999999
        let insertCount = 0; // 记录成功插入的数量
        let errorCount = 0; // 记录插入失败的数量

        incidents.forEach((incident, index) => {
            const newId = maxId + index + 1; // 生成新的 id
            const newIncId = `INC${newId}`; // 生成新的 Inc_id

            // 处理 Opened 和 Resolved 列
            const openedDate = incident.Opened ? convertToMySQLDate(incident.Opened) : null;
            const resolvedDate = incident.Resolved ? convertToMySQLDate(incident.Resolved) : null;

            const query = 'INSERT INTO incidents (id, Inc_id, Opened, Resolved, Opened_by, Priority, Project, Short_description, Description, Assigned_to, Assignment_group, Incident_state) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            
            db.query(query, [
                newId,
                newIncId,
                openedDate,
                resolvedDate,
                incident.Opened_by,
                incident.Priority,
                incident.Project,
                incident.Short_description,
                incident.Description,
                incident.Assigned_to,
                incident.Assignment_group,
                incident.Incident_state
            ], (err) => {
                if (err) {
                    console.error(`插入失败 (索引: ${index}):`, err); // 输出失败的索引和错误信息
                    errorCount++;
                } else {
                    insertCount++;
                }
            });
        });

        // 等待所有插入操作完成后返回响应
        setTimeout(() => {
            if (errorCount > 0) {
                return res.status(500).json({ message: `部分数据插入失败，成功插入 ${insertCount} 条记录，失败 ${errorCount} 条记录` });
            }
            res.json({ message: 'Incidents 数据上传成功', insertedCount: insertCount });
        }, 100); // 这里的 100 毫秒是为了确保所有插入操作完成
    });
});

// 将日期字符串转换为 MySQL DATETIME 格式，并将时间加8小时
function convertToMySQLDate(dateString) {
    const date = new Date(dateString);
    if (isNaN(date.getTime())) {
        return null; // 返回 null 以表示无效日期
    }
    // 将时间加8小时
    date.setHours(date.getHours() + 8);
    return date.toISOString().slice(0, 19).replace('T', ' '); // 转换为 'YYYY-MM-DD HH:mm:ss' 格式
}

// 将日期字符串转换为 MySQL DATETIME 格式，并将时间加8小时
function convertToMySQLDate(dateString) {
    const date = new Date(dateString);
    if (isNaN(date.getTime())) {
        return null; // 返回 null 以表示无效日期
    }
    // 将时间加8小时
    date.setHours(date.getHours() + 8);
    return date.toISOString().slice(0, 19).replace('T', ' '); // 转换为 'YYYY-MM-DD HH:mm:ss' 格式
}



// 处理上传的 requests 数据
app.post('/api/requests', (req, res) => {
    // 确保请求体是数组或单个对象
    const requests = Array.isArray(req.body) ? req.body : [req.body];

    // 检查请求数据格式
    if (requests.length === 0) {
        return res.status(400).json({ message: '请求数据格式不正确，应该是一个数组或单个对象' });
    }

    // 查询当前最大 id
    db.query('SELECT MAX(id) AS maxId FROM requests', (err, results) => {
        if (err) {
            return res.status(500).json({ error: '数据库查询失败' });
        }

        const maxId = results[0].maxId || 9999999; // 如果没有记录，设置为 9999999
        let insertCount = 0; // 记录成功插入的数量
        let errorCount = 0; // 记录插入失败的数量

        requests.forEach((request, index) => {
            const newId = maxId + index + 1; // 生成新的 id
            const newReqId = `REQ${newId}`; // 生成新的 Req_id

            // 处理 Opened 列
            const openedDate = request.Opened ? convertToMySQLDate(request.Opened) : null;

            // 默认 State 为 "Open"
            const state = request.State || 'Open';

            const query = 'INSERT INTO requests (id, Req_id, Item, Priority, Opened, Opened_by, State, Short_description, Description, Assigned_to, Assignment_group, Project) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            
            db.query(query, [
                newId,
                newReqId,
                request.Item,
                request.Priority,
                openedDate, // 使用转换后的日期
                request.Opened_by,
                state, // 使用默认的 State
                request.Short_description,
                request.Description,
                request.Assigned_to,
                request.Assignment_group,
                request.Project
            ], (err) => {
                if (err) {
                    console.error(`插入失败 (索引: ${index}):`, err); // 输出失败的索引和错误信息
                    errorCount++;
                } else {
                    insertCount++;
                }
            });
        });

        // 等待所有插入操作完成后返回响应
        setTimeout(() => {
            if (errorCount > 0) {
                return res.status(500).json({ message: `部分数据插入失败，成功插入 ${insertCount} 条记录，失败 ${errorCount} 条记录` });
            }
            res.json({ message: 'Requests 数据上传成功', insertedCount: insertCount });
        }, 100); // 这里的 100 毫秒是为了确保所有插入操作完成
    });
});


// 获取所有 incidents 数据
app.get('/api/incidents', (req, res) => {
    const { page = 1, limit = 20, search = '', openedBy = '', priority = '', project = '', shortDescription = '', assignedTo = '', assignmentGroup = '', incidentState = '' } = req.query;

    // 打印接收到的请求参数
    console.log('Received request with params:', {
        page,
        limit,
        search,
        openedBy,
        priority,
        project,
        shortDescription,
        assignedTo,
        assignmentGroup,
        incidentState
    });

    const offset = (page - 1) * limit;

    // 构建查询语句，添加新的搜索字段
    const query = `
        SELECT * FROM incidents
        WHERE (
            Inc_id LIKE ? 
            OR Priority LIKE ? 
            OR Project LIKE ? 
            OR Short_description LIKE ? 
            OR Assigned_to LIKE ? 
            OR Assignment_group LIKE ? 
            OR Incident_state LIKE ?
        )
        ${openedBy ? 'AND Opened_by = ?' : ''} -- 仅在 openedBy 有值时添加此条件
        ${priority ? 'AND Priority = ?' : ''} -- 仅在 priority 有值时添加此条件
        ${project ? 'AND Project = ?' : ''} -- 仅在 project 有值时添加此条件
        ${shortDescription ? 'AND Short_description = ?' : ''} -- 仅在 shortDescription 有值时添加此条件
        ${assignedTo ? 'AND Assigned_to = ?' : ''} -- 仅在 assignedTo 有值时添加此条件
        ${assignmentGroup ? 'AND Assignment_group = ?' : ''} -- 仅在 assignmentGroup 有值时添加此条件
        ${incidentState ? 'AND Incident_state = ?' : ''} -- 仅在 incidentState 有值时添加此条件 
        LIMIT ? OFFSET ?
    `;

    const searchTerm = `%${search}%`;
    const queryParams = [
        searchTerm, // Inc_id
        searchTerm, // Priority
        searchTerm, // Project
        searchTerm, // Short_description
        searchTerm, // Assigned_to
        searchTerm, // Assignment_group
        searchTerm, // Incident_state
        ...(openedBy ? [openedBy] : []), // 仅在 openedBy 有值时添加
        ...(priority ? [priority] : []), // 仅在 priority 有值时添加
        ...(project ? [project] : []), // 仅在 project 有值时添加
        ...(shortDescription ? [shortDescription] : []), // 仅在 shortDescription 有值时添加
        ...(assignedTo ? [assignedTo] : []), // 仅在 assignedTo 有值时添加
        ...(assignmentGroup ? [assignmentGroup] : []), // 仅在 assignmentGroup 有值时添加
        ...(incidentState ? [incidentState] : []), // 仅在 incidentState 有值时添加
        parseInt(limit),
        parseInt(offset)
    ];

    // 在执行查询之前打印 SQL 查询和参数
    console.log('Executing query:', query);
    console.log('With parameters:', queryParams);

    // 执行查询
    db.query(query, queryParams, (err, results) => {
        if (err) {
            console.error('Query error:', err);
            return res.status(500).json({ error: '数据库查询失败', details: err.message });
        }

        // 获取总记录数的查询
        const countQuery = `
            SELECT COUNT(*) AS total 
            FROM incidents 
            WHERE (
                Inc_id LIKE ? 
                OR Priority LIKE ? 
                OR Project LIKE ? 
                OR Short_description LIKE ? 
                OR Assigned_to LIKE ? 
                OR Assignment_group LIKE ? 
                OR Incident_state LIKE ?
            )
            ${openedBy ? 'AND Opened_by = ?' : ''} -- 仅在 openedBy 有值时添加此条件
            ${priority ? 'AND Priority = ?' : ''} -- 仅在 priority 有值时添加此条件
            ${project ? 'AND Project = ?' : ''} -- 仅在 project 有值时添加此条件
            ${shortDescription ? 'AND Short_description = ?' : ''} -- 仅在 shortDescription 有值时添加此条件
            ${assignedTo ? 'AND Assigned_to = ?' : ''} -- 仅在 assignedTo 有值时添加此条件
            ${assignmentGroup ? 'AND Assignment_group = ?' : ''} -- 仅在 assignmentGroup 有值时添加此条件
            ${incidentState ? 'AND Incident_state = ?' : ''} -- 仅在 incidentState 有值时添加此条件
        `;

        const countParams = [
            searchTerm, // Inc_id
            searchTerm, // Priority
            searchTerm, // Project
            searchTerm, // Short_description
            searchTerm, // Assigned_to
            searchTerm, // Assignment_group
            searchTerm, // Incident_state
            ...(openedBy ? [openedBy] : []), // 仅在 openedBy 有值时添加
            ...(priority ? [priority] : []), // 仅在 priority 有值时添加
            ...(project ? [project] : []), // 仅在 project 有值时添加
            ...(shortDescription ? [shortDescription] : []), // 仅在 shortDescription 有值时添加
            ...(assignedTo ? [assignedTo] : []), // 仅在 assignedTo 有值时添加
            ...(assignmentGroup ? [assignmentGroup] : []), // 仅在 assignmentGroup 有值时添加
            ...(incidentState ? [incidentState] : []), // 仅在 incidentState 有值时添加
        ];

        db.query(countQuery, countParams, (err, countResults) => {
            if (err) {
                console.error('Count query error:', err);
                return res.status(500).json({ error: '数据库查询失败', details: err.message });
            }

            const total = countResults[0].total;
            res.json({ 
                total, 
                incidents: results,
                page: parseInt(page),
                limit: parseInt(limit)
            });
        });
    });
});

// 批量删除 incidents 的 API
app.delete('/api/incidents', (req, res) => {
    const incIds = req.body.incIds; // 请求体中包含一个 incIds 数组，包含要删除的 Inc_id
    console.log('Deleting incidents:', incIds);
  
    // 检查 incIds 是否为数组且不为空
    if (!Array.isArray(incIds) || incIds.length === 0) {
      return res.status(400).json({ error: '无效的 Inc_id 列表' });
    }
  
    // 构造批量删除的 SQL 查询语句
    const placeholders = incIds.map(() => '?').join(', '); // 生成占位符字符串，例如 ?, ?, ?
    const sql = `DELETE FROM incidents WHERE Inc_id IN (${placeholders})`;
  
    // 执行删除操作
    db.query(sql, incIds, (err, results) => {
      if (err) {
        console.error('数据库查询失败:', err);
        return res.status(500).json({ error: '数据库操作失败', details: err.message });
      }
  
      // 检查是否成功删除了记录
      if (results.affectedRows === 0) {
        return res.status(404).json({ message: '未找到需要删除的记录' });
      }
  
      // 返回成功响应
      res.json({
        message: '记录删除成功',
        deletedCount: results.affectedRows
      });
    });
  });


//分配给自己的incident查询
// 获取用户昵称的API
app.post('/api/getNickname', async (req, res) => {
    const { username, userRole } = req.body; // 修改为 userRole

    // 根据角色决定查询哪个表和字段
    const userInfoTable = userRole === 'admin' ? 'admin_info' : 'user_info';
    const userIdField = userRole === 'admin' ? 'admin_id' : 'user_id';
    const userTable = userRole === 'admin' ? 'admins' : 'users';

    try {
        // 在对应的用户表中查找用户ID
        const [results] = await db.promise().query(
            `SELECT ${userIdField} AS user_id FROM ${userTable} WHERE username = ?`,
            [username]
        );

        if (results.length === 0) {
            return res.status(404).json({ error: '用户不存在', userRole, username });
        }

        const userId = results[0].user_id;

        // 在对应的用户信息表中查找昵称
        const [nicknameResults] = await db.promise().query(
            `SELECT nickname FROM ${userInfoTable} WHERE ${userIdField} = ?`,
            [userId]
        );

        if (nicknameResults.length === 0) {
            return res.status(404).json({ error: '用户信息不存在', userRole, username });
        }

        res.json({ nickname: nicknameResults[0].nickname });
    } catch (err) {
        console.error('查询昵称失败:', err);
        res.status(500).json({ error: '查询昵称失败', details: err.message, userRole, username });
    }
});







// 获取单个 incident 的 API
app.get('/api/incidents/:incId', (req, res) => {
    const { incId } = req.params;
    console.log('Fetching incident:', incId); // 添加日志

    db.query('SELECT * FROM incidents WHERE Inc_id = ?', [incId], (err, results) => {
        if (err) {
            console.error('Database query error:', err); // 添加错误日志
            return res.status(500).json({ error: '数据库查询失败', details: err.message });
        }
        if (results.length === 0) {
            return res.status(404).json({ message: 'Incident 未找到' });
        }
        res.json(results[0]);
    });
});

// 更新 incident 的 API
app.put('/api/incidents/:incId', (req, res) => {
    const { incId } = req.params;
    const updatedIncident = req.body;

    // 添加日志，查看接收到的数据
    console.log('Updating incident:', incId);
    console.log('Received data:', updatedIncident);

    // 格式化日期
    const formattedIncident = {
        ...updatedIncident,
        Opened: updatedIncident.Opened ? new Date(updatedIncident.Opened) : null,
        Resolved: updatedIncident.Resolved ? new Date(updatedIncident.Resolved) : null
    };

    const query = `
        UPDATE incidents SET 
        Opened = ?, Resolved = ?, Opened_by = ?, Priority = ?, Project = ?, 
        Short_description = ?, Description = ?, Assigned_to = ?, 
        Assignment_group = ?, Incident_state = ? 
        WHERE Inc_id = ?
    `;

    const values = [
        formattedIncident.Opened,
        formattedIncident.Resolved,
        formattedIncident.Opened_by,
        formattedIncident.Priority,
        formattedIncident.Project,
        formattedIncident.Short_description,
        formattedIncident.Description,
        formattedIncident.Assigned_to,
        formattedIncident.Assignment_group,
        formattedIncident.Incident_state,
        incId
    ];

    // 添加日志，查看实际执行的 SQL 参数
    console.log('Query values:', values);

    db.query(query, values, (err, result) => {
        if (err) {
            console.error('Update error:', err); // 添加错误日志
            return res.status(500).json({ 
                error: '更新失败', 
                details: err.message,
                sqlMessage: err.sqlMessage 
            });
        }

        // 检查是否有行被更新
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: '未找到要更新的 Incident' });
        }

        console.log('Update successful:', result); // 添加成功日志
        res.json({ 
            message: 'Incident 更新成功',
            affectedRows: result.affectedRows
        });
    });
});



// 获取所有 requests 数据
app.get('/api/requests', (req, res) => {
    const { 
        page = 1, 
        limit = 20, 
        searchReqId = '', 
        searchItem = '', 
        searchPriority = '', 
        searchOpenedBy = '', 
        searchState = '', 
        searchShortDescription = '', 
        searchAssignedTo = '', 
        searchAssignmentGroup = '', 
        searchProject = '' 
    } = req.query;

    // 打印接收到的请求参数
    console.log('Received request with params:', {
        page,
        limit,
        searchReqId,
        searchItem,
        searchPriority,
        searchOpenedBy,
        searchState,
        searchShortDescription,
        searchAssignedTo,
        searchAssignmentGroup,
        searchProject
    });

    const offset = (page - 1) * limit;

    // 构建查询语句
    const query = `
        SELECT * FROM requests
        WHERE (
            Req_id LIKE ?
            OR Item LIKE ?
            OR Opened_by LIKE ?
            OR Priority LIKE ?
            OR Project LIKE ?
            OR Short_description LIKE ?
            OR Assigned_to LIKE ?
            OR Assignment_group LIKE ?
            OR State LIKE ?
        )
        ${searchReqId ? 'AND Req_id LIKE ?' : ''} -- 仅在 searchReqId 有值时添加此条件
        ${searchItem ? 'AND Item LIKE ?' : ''} -- 仅在 searchItem 有值时添加此条件
        ${searchPriority ? 'AND Priority LIKE ?' : ''} -- 仅在 searchPriority 有值时添加此条件
        ${searchOpenedBy ? 'AND Opened_by LIKE ?' : ''} -- 仅在 searchOpenedBy 有值时添加此条件
        ${searchState ? 'AND State LIKE ?' : ''} -- 仅在 searchState 有值时添加此条件
        ${searchShortDescription ? 'AND Short_description LIKE ?' : ''} -- 仅在 searchShortDescription 有值时添加此条件
        ${searchAssignedTo ? 'AND Assigned_to LIKE ?' : ''} -- 仅在 searchAssignedTo 有值时添加此条件
        ${searchAssignmentGroup ? 'AND Assignment_group LIKE ?' : ''} -- 仅在 searchAssignmentGroup 有值时添加此条件
        ${searchProject ? 'AND Project LIKE ?' : ''} -- 仅在 searchProject 有值时添加此条件
        LIMIT ? OFFSET ?
    `;

    // 构建查询参数
    const searchTerm = `%${searchReqId || searchItem || searchPriority || searchOpenedBy || searchState || searchShortDescription || searchAssignedTo || searchAssignmentGroup || searchProject}%`;
    const queryParams = [
        searchTerm, // Req_id
        searchTerm, // Item
        searchTerm, // Opened_by
        searchTerm, // Priority
        searchTerm, // Project
        searchTerm, // Short_description
        searchTerm, // Assigned_to
        searchTerm, // Assignment_group
        searchTerm, // State
        ...(searchReqId ? [`%${searchReqId}%`] : []), // 仅在 searchReqId 有值时添加
        ...(searchItem ? [`%${searchItem}%`] : []), // 仅在 searchItem 有值时添加
        ...(searchPriority ? [`%${searchPriority}%`] : []), // 仅在 searchPriority 有值时添加
        ...(searchOpenedBy ? [`%${searchOpenedBy}%`] : []), // 仅在 searchOpenedBy 有值时添加
        ...(searchState ? [`%${searchState}%`] : []), // 仅在 searchState 有值时添加
        ...(searchShortDescription ? [`%${searchShortDescription}%`] : []), // 仅在 searchShortDescription 有值时添加
        ...(searchAssignedTo ? [`%${searchAssignedTo}%`] : []), // 仅在 searchAssignedTo 有值时添加
        ...(searchAssignmentGroup ? [`%${searchAssignmentGroup}%`] : []), // 仅在 searchAssignmentGroup 有值时添加
        ...(searchProject ? [`%${searchProject}%`] : []), // 仅在 searchProject 有值时添加
        parseInt(limit),
        parseInt(offset)
    ];

    // 打印 SQL 查询和参数
    console.log('Executing query:', query);
    console.log('With parameters:', queryParams);

    // 执行查询
    db.query(query, queryParams, (err, results) => {
        if (err) {
            console.error('Query error:', err);
            return res.status(500).json({ error: '数据库查询失败', details: err.message });
        }

        // 获取总记录数
        const countQuery = `
            SELECT COUNT(*) AS total 
            FROM requests 
            WHERE (
                Req_id LIKE ?
                OR Item LIKE ?
                OR Opened_by LIKE ?
                OR Priority LIKE ?
                OR Project LIKE ?
                OR Short_description LIKE ?
                OR Assigned_to LIKE ?
                OR Assignment_group LIKE ?
                OR State LIKE ?
            )
            ${searchReqId ? 'AND Req_id LIKE ?' : ''} -- 仅在 searchReqId 有值时添加此条件
            ${searchItem ? 'AND Item LIKE ?' : ''} -- 仅在 searchItem 有值时添加此条件
            ${searchPriority ? 'AND Priority LIKE ?' : ''} -- 仅在 searchPriority 有值时添加此条件
            ${searchOpenedBy ? 'AND Opened_by LIKE ?' : ''} -- 仅在 searchOpenedBy 有值时添加此条件
            ${searchState ? 'AND State LIKE ?' : ''} -- 仅在 searchState 有值时添加此条件
            ${searchShortDescription ? 'AND Short_description LIKE ?' : ''} -- 仅在 searchShortDescription 有值时添加此条件
            ${searchAssignedTo ? 'AND Assigned_to LIKE ?' : ''} -- 仅在 searchAssignedTo 有值时添加此条件
            ${searchAssignmentGroup ? 'AND Assignment_group LIKE ?' : ''} -- 仅在 searchAssignmentGroup 有值时添加此条件
            ${searchProject ? 'AND Project LIKE ?' : ''} -- 仅在 searchProject 有值时添加此条件
        `;

        const countParams = [
            searchTerm, // Req_id
            searchTerm, // Item
            searchTerm, // Opened_by
            searchTerm, // Priority
            searchTerm, // Project
            searchTerm, // Short_description
            searchTerm, // Assigned_to
            searchTerm, // Assignment_group
            searchTerm, // State
            ...(searchReqId ? [`%${searchReqId}%`] : []), // 仅在 searchReqId 有值时添加
            ...(searchItem ? [`%${searchItem}%`] : []), // 仅在 searchItem 有值时添加
            ...(searchPriority ? [`%${searchPriority}%`] : []), // 仅在 searchPriority 有值时添加
            ...(searchOpenedBy ? [`%${searchOpenedBy}%`] : []), // 仅在 searchOpenedBy 有值时添加
            ...(searchState ? [`%${searchState}%`] : []), // 仅在 searchState 有值时添加
            ...(searchShortDescription ? [`%${searchShortDescription}%`] : []), // 仅在 searchShortDescription 有值时添加
            ...(searchAssignedTo ? [`%${searchAssignedTo}%`] : []), // 仅在 searchAssignedTo 有值时添加
            ...(searchAssignmentGroup ? [`%${searchAssignmentGroup}%`] : []), // 仅在 searchAssignmentGroup 有值时添加
            ...(searchProject ? [`%${searchProject}%`] : []), // 仅在 searchProject 有值时添加
        ];

        db.query(countQuery, countParams, (err, countResults) => {
            if (err) {
                console.error('Count query error:', err);
                return res.status(500).json({ error: '数据库查询失败', details: err.message });
            }

            const total = countResults[0].total;
            res.json({ 
                total, 
                requests: results,
                page: parseInt(page),
                limit: parseInt(limit)
            });
        });
    });
});

// 批量删除 request 的 API
// 批量删除请求的 API
app.delete('/api/requests', (req, res) => {
    const reqIds = req.body.reqIds; // 请求体中包含一个 reqIds 数组，包含要删除的请求 ID
    console.log('Deleting requests:', reqIds);

    // 检查 reqIds 是否为数组且不为空
    if (!Array.isArray(reqIds) || reqIds.length === 0) {
        return res.status(400).json({ error: '无效的请求 ID 列表' });
    }

    // 构造批量删除的 SQL 查询语句
    const placeholders = reqIds.map(() => '?').join(', '); // 生成占位符字符串，例如 ?, ?, ?
    const sql = `DELETE FROM requests WHERE Req_id IN (${placeholders})`;

    // 执行删除操作
    db.query(sql, reqIds, (err, results) => {
        if (err) {
            console.error('Database query error:', err);
            return res.status(500).json({ error: '数据库操作失败', details: err.message });
        }

        // 检查是否成功删除了请求
        if (results.affectedRows === 0) {
            return res.status(404).json({ message: '未找到需要删除的请求' });
        }

        // 返回成功响应
        res.json({
            message: '请求删除成功',
            deletedCount: results.affectedRows
        });
    });
});












// 获取单个 request 的 API
app.get('/api/requests/:reqId', (req, res) => {
    const { reqId } = req.params;
    console.log('Fetching request:', reqId);

    db.query('SELECT * FROM requests WHERE Req_id = ?', [reqId], (err, results) => {
        if (err) {
            console.error('Database query error:', err);
            return res.status(500).json({ error: '数据库查询失败', details: err.message });
        }
        if (results.length === 0) {
            return res.status(404).json({ message: 'Request 未找到' });
        }
        res.json(results[0]);
    });
});

// 更新 request 的 API
app.put('/api/requests/:reqId', (req, res) => {
    const { reqId } = req.params;
    const updatedRequest = req.body;

    console.log('Updating request:', reqId);
    console.log('Received data:', updatedRequest);

    // 格式化日期
    const formattedRequest = {
        ...updatedRequest,
        Opened: updatedRequest.Opened ? new Date(updatedRequest.Opened) : null
    };

    const query = `
        UPDATE requests SET 
        Item = ?, Opened = ?, Opened_by = ?, Priority = ?, Project = ?, 
        Short_description = ?, Description = ?, Assigned_to = ?, 
        Assignment_group = ?, State = ? 
        WHERE Req_id = ?
    `;

    const values = [
        formattedRequest.Item,
        formattedRequest.Opened,
        formattedRequest.Opened_by,
        formattedRequest.Priority,
        formattedRequest.Project,
        formattedRequest.Short_description,
        formattedRequest.Description,
        formattedRequest.Assigned_to,
        formattedRequest.Assignment_group,
        formattedRequest.State,
        reqId
    ];

    console.log('Query values:', values);

    db.query(query, values, (err, result) => {
        if (err) {
            console.error('Update error:', err);
            return res.status(500).json({ 
                error: '更新失败', 
                details: err.message,
                sqlMessage: err.sqlMessage 
            });
        }

        if (result.affectedRows === 0) {
            return res.status(404).json({ message: '未找到要更新的 Request' });
        }

        console.log('Update successful:', result);
        res.json({ 
            message: 'Request 更新成功',
            affectedRows: result.affectedRows
        });
    });
});






// 获取评论列表
app.get('/api/incidents/:incId/comments', async (req, res) => {
    const { incId } = req.params;

    try {
        // 1. 先获取 incident 的 id
        const [incidents] = await db.promise().query(
            'SELECT id FROM incidents WHERE Inc_id = ?',
            [incId]
        );

        if (!incidents.length) {
            return res.status(404).json({ 
                error: '未找到对应的 Incident' 
            });
        }

        const incidentId = incidents[0].id;

        // 2. 获取评论列表
        const [comments] = await db.promise().query(`
            SELECT 
                ic.*,
                COALESCE(u.username, a.username) as username,
                CASE 
                    WHEN ic.admin_id IS NOT NULL THEN 'admin'
                    ELSE 'user'
                END as user_role
            FROM incident_comments ic
            LEFT JOIN users u ON ic.user_id = u.user_id
            LEFT JOIN admins a ON ic.admin_id = a.admin_id
            WHERE ic.incident_id = ?
            ORDER BY ic.created_at DESC
        `, [incidentId]);

        res.json(comments);

    } catch (error) {
        console.error('获取评论失败:', error);
        res.status(500).json({ 
            error: '获取评论失败',
            details: error.message 
        });
    }
});

// 添加新评论
app.post('/api/incidents/:incId/comments', async (req, res) => {
    const { incId } = req.params;
    const { comment, username, userRole } = req.body;

    try {
        // 1. 先获取 incident 的 id
        const [incidents] = await db.promise().query(
            'SELECT id FROM incidents WHERE Inc_id = ?',
            [incId]
        );

        if (!incidents.length) {
            return res.status(404).json({ 
                error: '未找到对应的 Incident' 
            });
        }

        const incidentId = incidents[0].id;

        // 2. 根据用户名和角色获取用户ID
        let userId = null;
        let adminId = null;

        if (userRole === 'admin') {
            const [admins] = await db.promise().query(
                'SELECT admin_id FROM admins WHERE username = ?',
                [username]
            );
            if (admins.length > 0) {
                adminId = admins[0].admin_id;
            }
        } else {
            const [users] = await db.promise().query(
                'SELECT user_id FROM users WHERE username = ?',
                [username]
            );
            if (users.length > 0) {
                userId = users[0].user_id;
            }
        }

        // 3. 插入评论
        const [result] = await db.promise().query(`
            INSERT INTO incident_comments 
            (incident_id, user_id, admin_id, comment)
            VALUES (?, ?, ?, ?)
        `, [incidentId, userId, adminId, comment]);

        // 4. 获取新插入的评论详情
        const [newComment] = await db.promise().query(`
            SELECT 
                ic.*,
                COALESCE(u.username, a.username) as username,
                CASE 
                    WHEN ic.admin_id IS NOT NULL THEN 'admin'
                    ELSE 'user'
                END as user_role
            FROM incident_comments ic
            LEFT JOIN users u ON ic.user_id = u.user_id
            LEFT JOIN admins a ON ic.admin_id = a.admin_id
            WHERE ic.id = ?
        `, [result.insertId]);

        res.json({
            message: '评论添加成功',
            comment: newComment[0]
        });

    } catch (error) {
        console.error('添加评论失败:', error);
        res.status(500).json({ 
            error: '添加评论失败',
            details: error.message 
        });
    }
});
// Incident评论列表管理
app.get('/api/incident_comments', async (req, res) => {
    const { page = 1, limit = 10, commentId, incidentId, userId, adminId, comment } = req.query; // 搜索参数

    try {
        // 构建 SQL 查询条件
        let whereClause = '1=1'; // 默认条件
        const params = [];

        if (commentId) {
            whereClause += ' AND ic.id = ?';
            params.push(parseInt(commentId));
        }
        if (incidentId) {
            whereClause += ' AND ic.incident_id = ?';
            params.push(parseInt(incidentId));
        }
        if (userId) {
            whereClause += ' AND ic.user_id = ?';
            params.push(parseInt(userId));
        }
        if (adminId) {
            whereClause += ' AND ic.admin_id = ?';
            params.push(parseInt(adminId));
        }
        if (comment) {
            whereClause += ' AND ic.comment LIKE ?';
            params.push(`%${comment}%`);
        }

        // 获取评论列表（带分页）
        const offset = (page - 1) * limit;
        const [comments] = await db.promise().query(`
            SELECT 
                ic.*,
                COALESCE(u.username, a.username) as username,
                CASE 
                    WHEN ic.admin_id IS NOT NULL THEN 'admin'
                    ELSE 'user'
                END as user_role
            FROM incident_comments ic
            LEFT JOIN users u ON ic.user_id = u.user_id
            LEFT JOIN admins a ON ic.admin_id = a.admin_id
            WHERE ${whereClause}
            ORDER BY ic.created_at DESC
            LIMIT ? OFFSET ?
        `, [...params, parseInt(limit), parseInt(offset)]);

        // 获取评论总数（用于分页）
        const [total] = await db.promise().query(
            `SELECT COUNT(*) as total FROM incident_comments ic WHERE ${whereClause}`,
            params
        );

        res.json({
            data: comments,
            total: total[0].total,
            page: parseInt(page),
            limit: parseInt(limit),
        });

    } catch (error) {
        console.error('获取评论失败:', error);
        res.status(500).json({ 
            error: '获取评论失败',
            details: error.message 
        });
    }
});

// 批量删除Incident评论
app.delete('/api/incident_comments', async (req, res) => {
    const { commentIds } = req.body; // 需要删除的评论 ID 数组

    try {
        // 检查评论是否存在
        const [comments] = await db.promise().query(
            'SELECT id FROM incident_comments WHERE id IN (?)',
            [commentIds]
        );

        if (comments.length !== commentIds.length) {
            return res.status(404).json({ 
                error: '部分评论未找到' 
            });
        }

        // 删除评论
        await db.promise().query(
            'DELETE FROM incident_comments WHERE id IN (?)',
            [commentIds]
        );

        res.json({ 
            message: '评论删除成功' 
        });

    } catch (error) {
        console.error('删除评论失败:', error);
        res.status(500).json({ 
            error: '删除评论失败',
            details: error.message 
        });
    }
});

// 更新Request评论

// 获取评论列表
app.get('/api/requests/:reqId/comments', async (req, res) => {
    const { reqId } = req.params;

    try {
        // 1. 先获取 request 的 id
        const [requests] = await db.promise().query(
            'SELECT id FROM requests WHERE Req_id = ?',
            [reqId]
        );

        if (!requests.length) {
            return res.status(404).json({ 
                error: '未找到对应的 Request' 
            });
        }

        const requestId = requests[0].id;

        // 2. 获取评论列表
        const [comments] = await db.promise().query(`
            SELECT 
                rc.*,
                COALESCE(u.username, a.username) as username,
                CASE 
                    WHEN rc.admin_id IS NOT NULL THEN 'admin'
                    ELSE 'user'
                END as user_role
            FROM request_comments rc
            LEFT JOIN users u ON rc.user_id = u.user_id
            LEFT JOIN admins a ON rc.admin_id = a.admin_id
            WHERE rc.request_id = ?
            ORDER BY rc.created_at DESC
        `, [requestId]);

        res.json(comments);

    } catch (error) {
        console.error('获取评论失败:', error);
        res.status(500).json({ 
            error: '获取评论失败',
            details: error.message 
        });
    }
});

// 添加新评论
app.post('/api/requests/:reqId/comments', async (req, res) => {
    const { reqId } = req.params;
    const { comment, username, userRole } = req.body;

    try {
        // 1. 先获取 request 的 id
        const [requests] = await db.promise().query(
            'SELECT id FROM requests WHERE Req_id = ?',
            [reqId]
        );

        if (!requests.length) {
            return res.status(404).json({ 
                error: '未找到对应的 Request' 
            });
        }

        const requestId = requests[0].id;

        // 2. 根据用户名和角色获取用户ID
        let userId = null;
        let adminId = null;

        if (userRole === 'admin') {
            const [admins] = await db.promise().query(
                'SELECT admin_id FROM admins WHERE username = ?',
                [username]
            );
            if (admins.length > 0) {
                adminId = admins[0].admin_id;
            }
        } else {
            const [users] = await db.promise().query(
                'SELECT user_id FROM users WHERE username = ?',
                [username]
            );
            if (users.length > 0) {
                userId = users[0].user_id;
            }
        }

        // 3. 插入评论
        const [result] = await db.promise().query(`
            INSERT INTO request_comments 
            (request_id, user_id, admin_id, comment)
            VALUES (?, ?, ?, ?)
        `, [requestId, userId, adminId, comment]);

        // 4. 获取新插入的评论详情
        const [newComment] = await db.promise().query(`
            SELECT 
                rc.*,
                COALESCE(u.username, a.username) as username,
                CASE 
                    WHEN rc.admin_id IS NOT NULL THEN 'admin'
                    ELSE 'user'
                END as user_role
            FROM request_comments rc
            LEFT JOIN users u ON rc.user_id = u.user_id
            LEFT JOIN admins a ON rc.admin_id = a.admin_id
            WHERE rc.id = ?
        `, [result.insertId]);

        res.json({
            message: '评论添加成功',
            comment: newComment[0]
        });

    } catch (error) {
        console.error('添加评论失败:', error);
        res.status(500).json({ 
            error: '添加评论失败',
            details: error.message 
        });
    }
});
//Request评论管理
//列表
app.get('/api/request_comments', async (req, res) => {
    const { page = 1, limit = 10, requestId, userId, adminId, comment } = req.query; // 搜索参数

    try {
        // 构建 SQL 查询条件
        let whereClause = '1=1'; // 默认条件
        const params = [];

        if (requestId) {
            whereClause += ' AND rc.request_id = ?';
            params.push(parseInt(requestId));
        }
        if (userId) {
            whereClause += ' AND rc.user_id = ?';
            params.push(parseInt(userId));
        }
        if (adminId) {
            whereClause += ' AND rc.admin_id = ?';
            params.push(parseInt(adminId));
        }
        if (comment) {
            whereClause += ' AND rc.comment LIKE ?';
            params.push(`%${comment}%`);
        }

        // 获取评论列表（带分页）
        const offset = (page - 1) * limit;
        const [comments] = await db.promise().query(`
            SELECT 
                rc.*,
                COALESCE(u.username, a.username) as username,
                CASE 
                    WHEN rc.admin_id IS NOT NULL THEN 'admin'
                    ELSE 'user'
                END as user_role
            FROM request_comments rc
            LEFT JOIN users u ON rc.user_id = u.user_id
            LEFT JOIN admins a ON rc.admin_id = a.admin_id
            WHERE ${whereClause}
            ORDER BY rc.created_at DESC
            LIMIT ? OFFSET ?
        `, [...params, parseInt(limit), parseInt(offset)]);

        // 获取评论总数（用于分页）
        const [total] = await db.promise().query(
            `SELECT COUNT(*) as total FROM request_comments rc WHERE ${whereClause}`,
            params
        );

        res.json({
            data: comments,
            total: total[0].total,
            page: parseInt(page),
            limit: parseInt(limit),
        });

    } catch (error) {
        console.error('获取评论失败:', error);
        res.status(500).json({ 
            error: '获取评论失败',
            details: error.message 
        });
    }
});
//批量删除
app.delete('/api/request_comments', async (req, res) => {
    const { commentIds } = req.body; // 需要删除的评论 ID 数组

    try {
        // 检查评论是否存在
        const [comments] = await db.promise().query(
            'SELECT id FROM request_comments WHERE id IN (?)',
            [commentIds]
        );

        if (comments.length !== commentIds.length) {
            return res.status(404).json({ 
                error: '部分评论未找到' 
            });
        }

        // 删除评论
        await db.promise().query(
            'DELETE FROM request_comments WHERE id IN (?)',
            [commentIds]
        );

        res.json({ 
            message: '评论删除成功' 
        });

    } catch (error) {
        console.error('删除评论失败:', error);
        res.status(500).json({ 
            error: '删除评论失败',
            details: error.message 
        });
    }
});

//数据可视化部分
//Incident统计数据
//原始数据
app.get('/api/incidents_stats', async (req, res) => {
    const { year, monthRange, project } = req.query;

    try {
        // 检查请求参数
        if (!year || !monthRange) {
            console.error('缺少必要的查询参数');
            return res.status(400).json({ error: '缺少必要的查询参数' });
        }

        // 解析月份范围
        const [startMonth, endMonth] = monthRange.split('-').map(Number);
        if (isNaN(startMonth) || isNaN(endMonth)) {
            console.error('月份范围无效');
            return res.status(400).json({ error: '月份范围无效' });
        }

        // 构建查询语句
        const query = `
            SELECT 
                MONTH(Opened) AS month,
                COUNT(*) AS total,
                SUM(CASE WHEN Incident_state IN ('Closed', 'Resolved') THEN 1 ELSE 0 END) AS closedResolved,
                SUM(CASE WHEN Incident_state IN ('Work In Progress', 'Open', 'Awaiting User Info') THEN 1 ELSE 0 END) AS inProgress
            FROM incidents
            WHERE YEAR(Opened) = ?
              AND MONTH(Opened) BETWEEN ? AND ?
              ${project ? 'AND Project = ?' : ''}
            GROUP BY MONTH(Opened)
            ORDER BY month;
        `;

        // 构建查询参数
        const queryParams = [year, startMonth, endMonth];
        if (project) queryParams.push(project);

        // 使用 async/await 执行查询
        const results = await new Promise((resolve, reject) => {
            db.query(query, queryParams, (err, results) => {
                if (err) {
                    console.error('数据库查询失败:', err);
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // 检查查询结果
        if (!results || results.length === 0) {
            console.warn('没有找到相关数据');
            return res.json({ data: [] });
        }

        // 返回结果
        res.json({ data: results });
    } catch (err) {
        console.error('查询失败:', err);
        res.status(500).json({ error: '数据库查询失败', details: err.message });
    }
});
//每月原始数据P1/P2/P3/P4
app.get('/api/incidents_stats/monthly/priority', async (req, res) => {
    const { year, month, project } = req.query; // 修改为接收 month 参数
    console.log('Received parameters:', { year, month, project }); // 打印接收到的参数

    try {
        // 检查请求参数
        if (!year || !month) {
            console.error('缺少必要的查询参数');
            return res.status(400).json({ error: '缺少必要的查询参数' });
        }

        // 解析月份
        const selectedMonth = Number(month);
        if (isNaN(selectedMonth) || selectedMonth < 1 || selectedMonth > 12) {
            console.error('月份无效');
            return res.status(400).json({ error: '月份无效' });
        }

        // 构建查询语句
        const query = `
            SELECT 
                MONTH(Opened) AS month,
                Priority,
                COUNT(*) AS total,
                SUM(CASE WHEN Incident_state IN ('Closed', 'Resolved') THEN 1 ELSE 0 END) AS closedResolved,
                SUM(CASE WHEN Incident_state IN ('Work In Progress', 'Open', 'Awaiting User Info') THEN 1 ELSE 0 END) AS inProgress
            FROM incidents
            WHERE YEAR(Opened) = ?
              AND MONTH(Opened) = ?
              ${project ? 'AND Project = ?' : ''}
            GROUP BY MONTH(Opened), Priority
            ORDER BY month, Priority;
        `;

        // 构建查询参数
        const queryParams = [year, selectedMonth];
        if (project) queryParams.push(project);

        // 使用 async/await 执行查询
        const results = await new Promise((resolve, reject) => {
            db.query(query, queryParams, (err, results) => {
                if (err) {
                    console.error('数据库查询失败:', err);
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // 检查查询结果
        if (!results || results.length === 0) {
            console.warn('没有找到相关数据');
            return res.json({ data: [] });
        }

        // 格式化结果
        const formattedResults = results.reduce((acc, row) => {
            const { month, Priority, total, closedResolved, inProgress } = row;
            if (!acc[month]) {
                acc[month] = {
                    month,
                    priorities: {
                        '1 - Critical': { total: 0, closedResolved: 0, inProgress: 0 },
                        '2 - High': { total: 0, closedResolved: 0, inProgress: 0 },
                        '3 - Moderate': { total: 0, closedResolved: 0, inProgress: 0 },
                        '4 - Low': { total: 0, closedResolved: 0, inProgress: 0 },
                    },
                };
            }
            acc[month].priorities[Priority] = { total, closedResolved, inProgress };
            return acc;
        }, {});

        // 将结果转换为数组
        const data = Object.values(formattedResults);

        // 返回结果
        res.json({ data });
    } catch (err) {
        console.error('查询失败:', err);
        res.status(500).json({ error: '数据库查询失败', details: err.message });
    }
});
//每周原始数据P1/P2/P3/P4
app.get('/api/incidents_stats/weekly', async (req, res) => {
    const { year, week, project, assignmentGroup } = req.query;
    console.log('Received parameters:', { year, week, project, assignmentGroup });

    try {
        // 参数校验
        if (!year || !week) {
            console.error('缺少必要的查询参数');
            return res.status(400).json({ error: '缺少必要的查询参数' });
        }

        const selectedWeek = Number(week);
        if (isNaN(selectedWeek) || selectedWeek < 1 || selectedWeek > 53) {
            console.error('周数无效');
            return res.status(400).json({ error: '周数无效' });
        }

        // 构建查询语句
        let query = `
            SELECT 
                YEAR(Opened) AS year,
                WEEK(Opened, 1) AS week,
                Priority,
                Assignment_group,
                COUNT(*) AS total,
                SUM(CASE WHEN Incident_state IN ('Closed', 'Resolved') THEN 1 ELSE 0 END) AS closedResolved,
                SUM(CASE WHEN Incident_state IN ('Work In Progress', 'Open', 'Awaiting User Info') THEN 1 ELSE 0 END) AS inProgress
            FROM incidents
            WHERE YEAR(Opened) = ?
              AND WEEK(Opened, 1) = ?
              AND Assignment_group IN ('Cloud-Administrators-L1', 'Cloud-Administrators-L2')
        `;

        // 添加项目筛选条件
        if (project) {
            query += ' AND Project = ?';
        }

        // 添加分配组筛选条件
        if (assignmentGroup) {
            query += ' AND Assignment_group = ?';
        }

        query += `
            GROUP BY YEAR(Opened), WEEK(Opened, 1), Priority, Assignment_group
            ORDER BY YEAR(Opened), WEEK(Opened, 1), Priority, Assignment_group
        `;

        // 构建查询参数
        const queryParams = [year, selectedWeek];
        if (project) queryParams.push(project);
        if (assignmentGroup) queryParams.push(assignmentGroup);

        // 查询第一个图表的数据
        const results1 = await new Promise((resolve, reject) => {
            db.query(query, queryParams, (err, results) => {
                if (err) {
                    console.error('数据库查询失败:', err);
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // 格式化第一个图表的数据
        const formattedResults1 = results1.reduce((acc, row) => {
            const { year, week, Priority, Assignment_group, total, closedResolved, inProgress } = row;
            if (!acc[week]) {
                acc[week] = {
                    year,
                    week,
                    priorities: {
                        '1 - Critical': { total: 0, closedResolved: 0, inProgress: 0, Assignment_group: '' },
                        '2 - High': { total: 0, closedResolved: 0, inProgress: 0, Assignment_group: '' },
                        '3 - Moderate': { total: 0, closedResolved: 0, inProgress: 0, Assignment_group: '' },
                        '4 - Low': { total: 0, closedResolved: 0, inProgress: 0, Assignment_group: '' },
                    },
                };
            }
            acc[week].priorities[Priority] = { total, closedResolved, inProgress, Assignment_group };
            return acc;
        }, {});

        // 查询第二个图表的数据
        let query2 = `
            SELECT 
                YEAR(Opened) AS year,
                WEEK(Opened, 1) AS week,
                CASE 
                    WHEN Opened_by = 'IntegrationSplunk' THEN '监控工具发现'
                    ELSE '用户报告'
                END AS incidentType,
                COUNT(*) AS total,
                SUM(CASE WHEN Incident_state IN ('Closed', 'Resolved') THEN 1 ELSE 0 END) AS closedResolved,
                SUM(CASE WHEN Incident_state IN ('Work In Progress', 'Open', 'Awaiting User Info') THEN 1 ELSE 0 END) AS inProgress
            FROM incidents
            WHERE YEAR(Opened) = ?
              AND WEEK(Opened, 1) = ?
              AND Opened_by IS NOT NULL
        `;

        // 添加项目筛选条件
        if (project) {
            query2 += ' AND Project = ?';
        }

        // 添加分配组筛选条件
        if (assignmentGroup) {
            query2 += ' AND Assignment_group = ?';
        }

        query2 += `
            GROUP BY YEAR(Opened), WEEK(Opened, 1), incidentType
            ORDER BY YEAR(Opened), WEEK(Opened, 1), incidentType
        `;

        const queryParams2 = [year, selectedWeek];
        if (project) queryParams2.push(project);
        if (assignmentGroup) queryParams2.push(assignmentGroup);

        const results2 = await new Promise((resolve, reject) => {
            db.query(query2, queryParams2, (err, results) => {
                if (err) {
                    console.error('数据库查询失败:', err);
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // 格式化第二个图表的数据
        const formattedResults2 = results2.reduce((acc, row) => {
            const { year, week, incidentType, total, closedResolved, inProgress } = row;
            if (!acc[week]) {
                acc[week] = {
                    year,
                    week,
                    incidentTypes: {
                        '监控工具发现': { total: 0, closedResolved: 0, inProgress: 0 },
                        '用户报告': { total: 0, closedResolved: 0, inProgress: 0 },
                    },
                };
            }
            acc[week].incidentTypes[incidentType] = { total, closedResolved, inProgress };
            return acc;
        }, {});

        // 返回两个图表的数据
        res.json({
            chart1: Object.values(formattedResults1),
            chart2: Object.values(formattedResults2),
        });
    } catch (err) {
        console.error('查询失败:', err);
        res.status(500).json({ error: '数据库查询失败', details: err.message });
    }
});










//request统计数据
app.get('/api/requests_stats', async (req, res) => {
    const { year, monthRange, project } = req.query;

    try {
        // 检查请求参数
        if (!year || !monthRange) {
            console.error('缺少必要的查询参数');
            return res.status(400).json({ error: '缺少必要的查询参数' });
        }

        // 解析月份范围
        const [startMonth, endMonth] = monthRange.split('-').map(Number);
        if (isNaN(startMonth) || isNaN(endMonth)) {
            console.error('月份范围无效');
            return res.status(400).json({ error: '月份范围无效' });
        }

        // 构建查询语句
        const query = `
            SELECT 
                MONTH(Opened) AS month,
                COUNT(*) AS total,
                SUM(CASE WHEN State = 'Closed Complete' THEN 1 ELSE 0 END) AS closedComplete,
                SUM(CASE WHEN State IN ('Closed Incomplete', 'Closed Skipped', 'Open') THEN 1 ELSE 0 END) AS closedInComplete
            FROM requests
            WHERE YEAR(Opened) = ?
              AND MONTH(Opened) BETWEEN ? AND ?
              ${project ? 'AND Project = ?' : ''}
            GROUP BY MONTH(Opened)
            ORDER BY month;
        `;

        // 构建查询参数
        const queryParams = [year, startMonth, endMonth];
        if (project) queryParams.push(project);

        // 使用 async/await 执行查询
        const results = await new Promise((resolve, reject) => {
            db.query(query, queryParams, (err, results) => {
                if (err) {
                    console.error('数据库查询失败:', err);
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // 检查查询结果
        if (!results || results.length === 0) {
            console.warn('没有找到相关数据');
            return res.json({ data: [] });
        }

        // 返回结果
        res.json({ data: results });
    } catch (err) {
        console.error('查询失败:', err);
        res.status(500).json({ error: '数据库查询失败', details: err.message });
    }
});
//每月原始数据P1/P2/P3/P4
app.get('/api/requests_stats/monthly/priority', async (req, res) => {
    const { year, month, project } = req.query; // 接收 year、month 和可选的 project 参数
    console.log('Received parameters:', { year, month, project }); // 打印接收到的参数

    try {
        // 检查请求参数
        if (!year || !month) {
            console.error('缺少必要的查询参数');
            return res.status(400).json({ error: '缺少必要的查询参数' });
        }

        // 解析月份
        const selectedMonth = Number(month);
        if (isNaN(selectedMonth) || selectedMonth < 1 || selectedMonth > 12) {
            console.error('月份无效');
            return res.status(400).json({ error: '月份无效' });
        }

        // 构建查询语句
        const query = `
            SELECT 
                MONTH(Opened) AS month,
                Priority,
                COUNT(*) AS total,
                SUM(CASE WHEN State IN ('Closed Complete') THEN 1 ELSE 0 END) AS closedResolved,
                SUM(CASE WHEN State IN ('Closed Incomplete','Closed Skipped','Open') THEN 1 ELSE 0 END) AS inProgress
            FROM requests
            WHERE YEAR(Opened) = ?
              AND MONTH(Opened) = ?
              ${project ? 'AND Project = ?' : ''}
            GROUP BY MONTH(Opened), Priority
            ORDER BY month, Priority;
        `;

        // 构建查询参数
        const queryParams = [year, selectedMonth];
        if (project) queryParams.push(project);

        // 使用 async/await 执行查询
        const results = await new Promise((resolve, reject) => {
            db.query(query, queryParams, (err, results) => {
                if (err) {
                    console.error('数据库查询失败:', err);
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // 检查查询结果
        if (!results || results.length === 0) {
            console.warn('没有找到相关数据');
            return res.json({ data: [] });
        }

        // 格式化结果
        const formattedResults = results.reduce((acc, row) => {
            const { month, Priority, total, closedResolved, inProgress } = row;
            if (!acc[month]) {
                acc[month] = {
                    month,
                    priorities: {
                        '1 - Critical': { total: 0, closedResolved: 0, inProgress: 0 },
                        '2 - High': { total: 0, closedResolved: 0, inProgress: 0 },
                        '3 - Moderate': { total: 0, closedResolved: 0, inProgress: 0 },
                        '4 - Low': { total: 0, closedResolved: 0, inProgress: 0 },
                    },
                };
            }
            acc[month].priorities[Priority] = { total, closedResolved, inProgress };
            return acc;
        }, {});

        // 将结果转换为数组
        const data = Object.values(formattedResults);

        // 返回结果
        res.json({ data });
    } catch (err) {
        console.error('查询失败:', err);
        res.status(500).json({ error: '数据库查询失败', details: err.message });
    }
});
//每周原始数据P1/P2/P3/P4
app.get('/api/requests_stats/weekly', async (req, res) => {
    const { year, week, project, assignmentGroup } = req.query;
    console.log('Received parameters:', { year, week, project, assignmentGroup });

    try {
        // 参数校验
        if (!year || !week) {
            console.error('缺少必要的查询参数');
            return res.status(400).json({ error: '缺少必要的查询参数' });
        }

        const selectedWeek = Number(week);
        if (isNaN(selectedWeek) || selectedWeek < 1 || selectedWeek > 53) {
            console.error('周数无效');
            return res.status(400).json({ error: '周数无效' });
        }

        // 第一个查询：按优先级分类
        let query1 = `
            SELECT 
                YEAR(Opened) AS year,
                WEEK(Opened, 1) AS week,
                Priority,
                Assignment_group,
                COUNT(*) AS total,
                SUM(CASE 
                    WHEN State = 'Closed Complete' THEN 1 
                    ELSE 0 
                END) AS closedResolved,
                SUM(CASE 
                    WHEN State IN ('Closed Incomplete', 'Closed Skipped', 'Open') THEN 1 
                    ELSE 0 
                END) AS inProgress
            FROM requests
            WHERE YEAR(Opened) = ?
              AND WEEK(Opened, 1) = ?
        `;

        // 添加项目筛选条件
        if (project) {
            query1 += ' AND Project = ?';
        }

        // 添加分配组筛选条件
        if (assignmentGroup === "null") {
            query1 += ` AND (Assignment_group IS NULL OR Assignment_group = '')`;
        } else if (assignmentGroup) {
            query1 += ' AND Assignment_group = ?';
        } else {
            query1 += `
                AND (
                    Assignment_group IN (
                        'Cloud-Administrators-L1', 
                        'Cloud-Administrators-L2', 
                        'Cloud-Administrators-Tech Lead'
                    ) 
                    OR Assignment_group IS NULL 
                    OR Assignment_group = ''
                )
            `;
        }

        query1 += `
            GROUP BY YEAR(Opened), WEEK(Opened, 1), Priority, Assignment_group
            ORDER BY YEAR(Opened), WEEK(Opened, 1), Priority, Assignment_group
        `;

        // 构建查询参数
        const queryParams1 = [year, selectedWeek];
        if (project) queryParams1.push(project);
        if (assignmentGroup && assignmentGroup !== "null") queryParams1.push(assignmentGroup);

        // 执行第一个查询
        const results1 = await new Promise((resolve, reject) => {
            db.query(query1, queryParams1, (err, results) => {
                if (err) {
                    console.error('数据库查询失败:', err);
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // 格式化第一个查询的结果
        const formattedResults1 = results1.reduce((acc, row) => {
            const { year, week, Priority, Assignment_group, total, closedResolved, inProgress } = row;
            if (!acc[week]) {
                acc[week] = {
                    year,
                    week,
                    priorities: {
                        '1 - Critical': { total: 0, closedResolved: 0, inProgress: 0, Assignment_group: '' },
                        '2 - High': { total: 0, closedResolved: 0, inProgress: 0, Assignment_group: '' },
                        '3 - Moderate': { total: 0, closedResolved: 0, inProgress: 0, Assignment_group: '' },
                        '4 - Low': { total: 0, closedResolved: 0, inProgress: 0, Assignment_group: '' },
                    },
                };
            }
            acc[week].priorities[Priority] = { total, closedResolved, inProgress, Assignment_group };
            return acc;
        }, {});

        // 第二个查询：按 Item 类型分类
        let query2 = `
            SELECT 
                YEAR(Opened) AS year,
                WEEK(Opened, 1) AS week,
                Item,
                COUNT(*) AS total,
                SUM(CASE 
                    WHEN State = 'Closed Complete' THEN 1 
                    ELSE 0 
                END) AS closedResolved,
                SUM(CASE 
                    WHEN State IN ('Closed Incomplete', 'Closed Skipped', 'Open') THEN 1 
                    ELSE 0 
                END) AS inProgress
            FROM requests
            WHERE YEAR(Opened) = ?
              AND WEEK(Opened, 1) = ?
        `;

        // 添加项目筛选条件
        if (project) {
            query2 += ' AND Project = ?';
        }

        // 添加分配组筛选条件
        if (assignmentGroup === "null") {
            query2 += ` AND (Assignment_group IS NULL OR Assignment_group = '')`;
        } else if (assignmentGroup) {
            query2 += ' AND Assignment_group = ?';
        }

        query2 += `
            GROUP BY YEAR(Opened), WEEK(Opened, 1), Item
            ORDER BY YEAR(Opened), WEEK(Opened, 1), Item
        `;

        // 构建查询参数
        const queryParams2 = [year, selectedWeek];
        if (project) queryParams2.push(project);
        if (assignmentGroup && assignmentGroup !== "null") queryParams2.push(assignmentGroup);

        // 执行第二个查询
        const results2 = await new Promise((resolve, reject) => {
            db.query(query2, queryParams2, (err, results) => {
                if (err) {
                    console.error('数据库查询失败:', err);
                    reject(err);
                } else {
                    resolve(results);
                }
            });
        });

        // 格式化第二个查询的结果
        const formattedResults2 = results2.reduce((acc, row) => {
            const { year, week, Item, total, closedResolved, inProgress } = row;
            if (!acc[week]) {
                acc[week] = {
                    year,
                    week,
                    items: {
                        'Onboarding, Provisioning and Decommissioning Request': { total: 0, closedResolved: 0, inProgress: 0 },
                        'Project Request': { total: 0, closedResolved: 0, inProgress: 0 },
                        'Report Request': { total: 0, closedResolved: 0, inProgress: 0 },
                        'Technical and Configuration Request': { total: 0, closedResolved: 0, inProgress: 0 },
                    },
                };
            }
            acc[week].items[Item] = { total, closedResolved, inProgress };
            return acc;
        }, {});

        // 返回两个图表的数据
        res.json({
            chart1: Object.values(formattedResults1),
            chart2: Object.values(formattedResults2),
        });
    } catch (err) {
        console.error('查询失败:', err);
        res.status(500).json({ error: '数据库查询失败', details: err.message });
    }
});


